﻿/*
 *  Copyright 2009 Alessandro Pilotti
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation; either version 2.1 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA 
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Management.Automation;
using System.Data.SqlClient;
using System.Data;

namespace AlexPilotti.PowerQuery.Commands
{
    public enum ExecuteSQLResultType { NonQuery, DataTable, DataSet }

    [Cmdlet("Execute", "SQL")]
    public class ExecuteSQLCommand : Cmdlet
    {
        SqlConnection conn;

        [Parameter(Position = 0, Mandatory = true)]
        [ValidateNotNullOrEmpty]
        public string ConnectionString { get; set; }

        [Parameter(Position = 1, Mandatory = true, ValueFromPipeline = true)]
        [ValidateNotNullOrEmpty]
        public string SQLText { get; set; }

        [Parameter(Position = 2)]
        public ExecuteSQLResultType ResultType { get; set; }

        public ExecuteSQLCommand()
        {
            ResultType = ExecuteSQLResultType.DataTable;
        }

        protected override void BeginProcessing()
        {
            base.BeginProcessing();

            conn = new SqlConnection(ConnectionString);
        }

        protected override void ProcessRecord()
        {
            base.ProcessRecord();

            object o = null;
            
            using (SqlCommand cmd = new SqlCommand(SQLText, conn))
            {
                switch (ResultType)
                {
                    case ExecuteSQLResultType.NonQuery:
                        o = cmd.ExecuteNonQuery();
                        break;
                    case ExecuteSQLResultType.DataSet:
                        {
                            DataSet ds = new DataSet();
                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                                da.Fill(ds);
                            o = ds;
                            break;
                        }
                    case ExecuteSQLResultType.DataTable:
                        {
                            DataTable dt = new DataTable();
                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                                da.Fill(dt);
                            o = dt;
                            break;
                        }
                }

            }

            WriteObject(o);
        }

        protected override void EndProcessing()
        {
            base.EndProcessing();

            if (conn != null)
            {
                conn.Close();
                conn = null;
            }
        }
    }
}
